package org.apache.lucene.analysis.kr.utils;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.pool.ObjectPool;
import org.apache.commons.pool.impl.GenericObjectPool;
import org.apache.lucene.analysis.kr.utils.KoreanEnv;
import org.apache.lucene.analysis.kr.morph.MorphException;
import org.apache.lucene.analysis.kr.morph.WordDBEntity;
import org.apache.lucene.analysis.kr.morph.WordEntry;

import risk.analysis.Compounds;
import risk.analysis.Mistyping;
import risk.analysis.Synonym;


public class DBUtil{

	private static final String DB_PROPERTY = "korean_analysis.properties";

	private static String DB_DBMS = "";
	private static String DB_DRIVER = "";
	private static String DB_URL = "";
	private static String DB_USER  = "";
	private static String DB_PASS = "";
	private static String DB_TABLE_NAME = "";
	private static String MIS_DB_TABLE_NAME = "";
	private static String SYN_DB_TABLE_NAME = "";
	private static String COM_DB_TABLE_NAME = "";

	public DBUtil(){

		Properties props = null;
		try {
			props = loadProperties(props);
		} catch (MorphException e) {
		}

		DB_DBMS = props.getProperty("dbms");
		DB_DRIVER = props.getProperty("driver");
		DB_URL = props.getProperty("url");
		DB_USER  = props.getProperty("user");
		DB_PASS = props.getProperty("pass");
		DB_TABLE_NAME = props.getProperty("table_name") == null || props.getProperty("table_name").equals("") ? "DICTIONARY" : props.getProperty("table_name");
		MIS_DB_TABLE_NAME = props.getProperty("mis_table_name") == null || props.getProperty("table_name").equals("") ? "USER_MIS_DIC" : props.getProperty("mis_table_name");
		SYN_DB_TABLE_NAME = props.getProperty("syn_table_name") == null || props.getProperty("syn_table_name").equals("") ? "USER_SYN_DIC" : props.getProperty("syn_table_name");
		COM_DB_TABLE_NAME = props.getProperty("com_table_name") == null || props.getProperty("com_table_name").equals("") ? "USER_COM_DIC" : props.getProperty("com_table_name");
	}
	
	
	public ArrayList loadDictionary() throws Exception{
		
		ArrayList dicList = new ArrayList();
		
		Connection conn =  null;
		Statement stmt = null;
		ResultSet rs = null;
		try{ 
			WordDBEntity entity;
			conn = getDirectConn();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT WORD_NO, WORD_NM, use_yn FROM "+DB_TABLE_NAME);
			while(rs.next()){
				entity = new WordDBEntity();
				entity.setSeq(rs.getInt(1));
				entity.setWord(rs.getString(2));
				entity.setFeatures("100000000X");
				entity.setUse_yn(rs.getString(3));
				dicList.add(entity);
			}			
		} catch(Exception e){
			e.printStackTrace();
			throw e;
		} finally {
			if(rs != null) 	try{rs.close();} catch(Exception e){}
			if(stmt != null) try{stmt.close();} catch(Exception e){}
			if(conn != null) try{conn.close();} catch(Exception e){}
		}
		
		return dicList;
	}
	
	public ArrayList<Mistyping> loadMisDictionary() throws Exception{
		
		ArrayList<Mistyping> misDicList = new ArrayList<Mistyping>();
		
		Connection conn =  null;
		Statement stmt = null;
		ResultSet rs = null;
		try{ 
			Mistyping entity;
			conn = getDirectConn();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT SEQ, mis_word, org_word FROM "+MIS_DB_TABLE_NAME);
			while(rs.next()){
				entity = new Mistyping();
				entity.setSeq(rs.getInt(1));
				entity.setMis_word(rs.getString(2));
				entity.setOrg_word(rs.getString(3));
				misDicList.add(entity);
			}			
		} catch(Exception e){
			e.printStackTrace();
			throw e;
		} finally {
			if(rs != null) 	try{rs.close();} catch(Exception e){}
			if(stmt != null) try{stmt.close();} catch(Exception e){}
			if(conn != null) try{conn.close();} catch(Exception e){}
		}
		
		return misDicList;
	}
	
	public ArrayList<Compounds> loadComDictionary() throws Exception{
		ArrayList<Compounds> comDicList = new ArrayList<Compounds>();
		
		Connection conn =  null;
		Statement stmt = null;
		ResultSet rs = null;
		try{ 
			Compounds entity;
			conn = getDirectConn();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT SEQ, org_word, com_word FROM "+COM_DB_TABLE_NAME);
			while(rs.next()){
				entity = new Compounds();
				entity.setSeq(rs.getInt(1));
				entity.setOrg_word(rs.getString(2));
				entity.setCom_word(rs.getString(3));
				comDicList.add(entity);
			}			
		} catch(Exception e){
			e.printStackTrace();
			throw e;
		} finally {
			if(rs != null) 	try{rs.close();} catch(Exception e){}
			if(stmt != null) try{stmt.close();} catch(Exception e){}
			if(conn != null) try{conn.close();} catch(Exception e){}
		}
		
		return comDicList;
	}
	
	public ArrayList<Synonym> loadSynDictionary() throws Exception{
		ArrayList<Synonym> synDicList = new ArrayList<Synonym>();
		
		Connection conn =  null;
		Statement stmt = null;
		ResultSet rs = null;
		try{ 
			Synonym entity;
			conn = getDirectConn();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT SEQ, org_word, syn_word FROM "+SYN_DB_TABLE_NAME);
			while(rs.next()){
				entity = new Synonym();
				entity.setSeq(rs.getInt(1));
				entity.setOrg_word(rs.getString(2));
				entity.setSyn_word(rs.getString(3));
				synDicList.add(entity);
			}			
		} catch(Exception e){
			e.printStackTrace();
			throw e;
		} finally {
			if(rs != null) 	try{rs.close();} catch(Exception e){}
			if(stmt != null) try{stmt.close();} catch(Exception e){}
			if(conn != null) try{conn.close();} catch(Exception e){}
		}
		
		return synDicList;
	}
	

	// cvs 에서 total.dic, extension.dic 을 DICTIONARY_ORG 테이블에 미리 넣어 둘것!! (한글 문제 때문..)
	public ArrayList<WordDBEntity> loadDictionaryOrg() throws Exception{
		
		ArrayList<WordDBEntity> dicList = new ArrayList<WordDBEntity>();
		
		Connection conn =  null;
		Statement stmt = null;
		ResultSet rs = null;
		try{ 
			WordDBEntity entity;
			conn = getDirectConn();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT seq, word, features FROM DICTIONARY_ORG WHERE SEQ > 35512 ORDER BY SEQ");
			while(rs.next()){
				entity = new WordDBEntity();
				entity.setSeq(rs.getInt(1));
				entity.setWord(rs.getString(2));
				entity.setFeatures(rs.getString(3));
				dicList.add(entity);
			}			
		} catch(Exception e){
			e.printStackTrace();
			throw e;
		} finally {
			if(rs != null) 	try{rs.close();} catch(Exception e){}
			if(stmt != null) try{stmt.close();} catch(Exception e){}
			if(conn != null) try{conn.close();} catch(Exception e){}
		}
		
		return dicList;
	}
	
	
	// cvs 에서 사전 업데이트 돼었을 경우 실행시킬 메소드.
	// 기존 테이블을 DICTIONARY2 에 복사해 놓고 작업 할것!!!
	public void updateDictionary(ArrayList<WordDBEntity> dicList) throws Exception{
		Connection conn =  null;
		Statement stmt = null;
		ResultSet rs = null;
		boolean already = false;
		int nextSEQ=0;
		String query = "";
		try{ 
			conn = getDirectConn();
			stmt = conn.createStatement();
			
			rs = stmt.executeQuery("SELECT MAX(SEQ) FROM DICTIONARY2");
			
			if(rs.next()){
				nextSEQ = rs.getInt(1);
			}
			
			for(WordDBEntity wdbe:dicList) {				
				
					System.out.println(wdbe.getSeq() + ", " +  wdbe.getWord() + ", " + wdbe.getFeatures());
					
					query = "SELECT SEQ, WORD, FEATURES FROM DICTIONARY2 WHERE WORD = '"+wdbe.getWord()+"'";
					//System.out.println(query);
					// 해당 단어가 존재 하는지 확인
					rs = stmt.executeQuery(query);
					already = false;
					if(rs.next()){
						if(!rs.getString("FEATURES").equals(wdbe.getFeatures())){
							// 존재하고 특성이 다르면 특성을 갱신
							stmt.executeUpdate("UPDATE DICTIONARY2 SET FEATURES = '"+wdbe.getFeatures()+"' WHERE SEQ = " + rs.getInt("SEQ"));
						}
						already = true;
					}
					
					if(!already && !wdbe.getWord().equals("") && wdbe.getFeatures() != null && !wdbe.getFeatures().equals("") && wdbe.getFeatures().length() > 0){
						// 존재하지 않으면 등록.
						query = "INSERT INTO DICTIONARY2 (SEQ, WORD, FEATURES, LAN, WDATE, WNAME, ORG) VALUES ("+ (++nextSEQ) +", '"+wdbe.getWord()+"', '"+wdbe.getFeatures()+"', 'ko', FROM_UNIXTIME(unix_timestamp(), '%Y%m%d'), 'system', 1 )";
						System.out.println("신규 등록 : " + query);
						stmt.executeUpdate(query);
					}
			}	
		} catch(Exception e){
			e.printStackTrace();
			throw e;
		} finally {
			if(rs != null) 	try{rs.close();} catch(Exception e){}
			if(stmt != null) try{stmt.close();} catch(Exception e){}
			if(conn != null) try{conn.close();} catch(Exception e){}
		}
	}
	
	public Connection getDirectConn()throws Exception{		
		Connection conn = null;
		ObjectPool objectPool = null;
		PoolableConnectionFactory poolFactory;	
		PoolingDataSource pds = null;

		//objectPool = new GenericObjectPool(null, 5);
		objectPool = new GenericObjectPool(null, 5, (byte)1, 2000, 10, 0, false, false, 10000, 5, 1800000L, true);
		
		ConnectionFactory conFactory = new DBConnFactory(DB_DRIVER, DB_URL, DB_USER, DB_PASS);
		
		poolFactory = new PoolableConnectionFactory(conFactory, objectPool,
				null, null, true, true);

		poolFactory.setDefaultAutoCommit(true);
		
		if(DB_DBMS.equals("mysql")){
			poolFactory.setValidationQuery("select 1");
			poolFactory.setDefaultReadOnly(false);
		}

		pds = new PoolingDataSource(objectPool);
		conn = pds.getConnection();

		if(DB_DBMS.equals("mysql")){
			conn.setReadOnly(false);
		}
		
		return conn;
	}
	
	private Properties loadProperties(Properties def) throws MorphException {
		Properties properties = new Properties();

		if (def != null) {
			properties = new Properties(def);
		}

		File file = null;
		try {
			file = FileUtil.getClassLoaderFile(DB_PROPERTY);
			if (file != null) {
				properties.load(new FileInputStream(file));
				return properties;
			}
			
			byte[] in = FileUtil.readByteFromCurrentJar(DB_PROPERTY);
			properties.load(new ByteArrayInputStream(in));
		} catch (Exception e) {
			throw new MorphException("Failure while trying to load properties file " + file.getPath(), e);
		}
		return properties;
	}
}
